package dyyx.util;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.commons.lang3.StringUtils;

import dyyx.dto.Colinfo;
import dyyx.dto.Result;
import dyyx.jdbc.RowBuilder;

public abstract class JdbcUtil {
	
	private static final int MAX_ROWS = 200;
	private static final int MAX_ROWS_MAX = 10000;
	


	public static Connection getConnection(String driver, String url, String user, String pwd) {
		try {
			Class.forName(driver);
			Connection conn =  DriverManager.getConnection(url, user, pwd);
			conn.setReadOnly(true);
			return conn;
		} catch (Throwable e) {
			throw new RuntimeException("getConnection error", e);
		}
	}

	public static void close(AutoCloseable obj) {
		if (obj == null) {
			return;
		}
		try {
			obj.close();
		} catch (Throwable e) {
			// ignore
		}
	}

	public static Object queryOne(Connection cn, String sql, Object[] params, RowBuilder builder) throws SQLException {
		if (cn == null || builder == null) {
			return null;
		}

		PreparedStatement pstmt = null;
		ResultSet rs = null;

		try {
			pstmt = cn.prepareStatement(sql);
			if (params != null && params.length > 0) {
				for (int i = 0; i < params.length; i++) {
					pstmt.setObject((i + 1), params[i]);
				}
			}
			pstmt.setMaxRows(1);
			rs = pstmt.executeQuery();

			while (rs.next()) {
				return builder.build(rs);
			}
			return null;
		} finally {
			close(rs);
			close(pstmt);
		}

	}

	public static int queryInt(Connection cn, String sql, Object[] params) throws SQLException {
		if (cn == null || StringUtils.isBlank(sql)) {
			return 0;
		}

		PreparedStatement pstmt = null;
		ResultSet rs = null;

		try {
			pstmt = cn.prepareStatement(sql);
			if (params != null && params.length > 0) {
				for (int i = 0; i < params.length; i++) {
					pstmt.setObject((i + 1), params[i]);
				}
			}
			pstmt.setMaxRows(1);
			rs = pstmt.executeQuery();
			while (rs.next()) {
				return rs.getInt(1);
			}
			return 0;
		} finally {
			close(rs);
			close(pstmt);
		}

	}

	public static long queryLong(Connection cn, String sql, Object[] params) throws SQLException {
		if (cn == null || StringUtils.isBlank(sql)) {
			return 0;
		}

		PreparedStatement pstmt = null;
		ResultSet rs = null;

		try {
			pstmt = cn.prepareStatement(sql);
			if (params != null && params.length > 0) {
				for (int i = 0; i < params.length; i++) {
					pstmt.setObject((i + 1), params[i]);
				}
			}
			pstmt.setMaxRows(1);
			rs = pstmt.executeQuery();
			while (rs.next()) {
				return rs.getLong(1);
			}
			return 0;
		} finally {
			close(rs);
			close(pstmt);
		}

	}

	public static Map<String, String> queryOneAsMap(Connection cn, String sql, Object[] params) throws SQLException {
		if (cn == null || StringUtils.isBlank(sql)) {
			return null;
		}

		PreparedStatement pstmt = null;
		ResultSet rs = null;

		try {
			pstmt = cn.prepareStatement(sql);
			if (params != null && params.length > 0) {
				for (int i = 0; i < params.length; i++) {
					pstmt.setObject((i + 1), params[i]);
				}
			}
			pstmt.setMaxRows(1);

			rs = pstmt.executeQuery();
			String[] cols = getColumns(rs);
			if (cols == null) {
				return null;
			}
			int count = cols.length;
			if (count <= 0) {
				return null;
			}

			while (rs.next()) {
				return getRowAsMap(rs, cols, count);
			}
			return null;
		} finally {
			close(rs);
			close(pstmt);
		}

	}

	public static List query(Connection cn, String sql, Object[] params, RowBuilder builder, int maxRows)
			throws SQLException {
		if (cn == null || builder == null) {
			return null;
		}
		PreparedStatement pstmt = null;
		ResultSet rs = null;

		try {
			pstmt = cn.prepareStatement(sql);
			if (params != null && params.length > 0) {
				for (int i = 0; i < params.length; i++) {
					pstmt.setObject((i + 1), params[i]);
				}
			}
			if (maxRows > 0) {
				pstmt.setMaxRows(maxRows);
			}

			rs = pstmt.executeQuery();
			List list = new ArrayList();
			Object obj = null;
			while (rs.next()) {
				obj = builder.build(rs);
				list.add(obj);
			}
			return list;
		} finally {
			close(rs);
			close(pstmt);
		}
	}

	public static List<Map<String, String>> queryAsMap(Connection cn, String sql, Object[] params, int maxRows)
			throws SQLException {
		if (cn == null || StringUtils.isBlank(sql)) {
			return null;
		}

		PreparedStatement pstmt = null;
		ResultSet rs = null;

		try {
			pstmt = cn.prepareStatement(sql);
			if (params != null && params.length > 0) {
				for (int i = 0; i < params.length; i++) {
					pstmt.setObject((i + 1), params[i]);
				}
			}
			if (maxRows > 0) {
				pstmt.setMaxRows(maxRows);
			}
			rs = pstmt.executeQuery();

			String[] cols = getColumns(rs);
			if (cols == null) {
				return null;
			}
			int count = cols.length;
			if (count <= 0) {
				return null;
			}

			List<Map<String, String>> list = new ArrayList<Map<String, String>>();
			while (rs.next()) {
				list.add(getRowAsMap(rs, cols, count));
			}
			return list;
		} finally {
			close(rs);
			close(pstmt);
		}
	}

	public static int executeUpdate(Connection cn, String sql, Object[] params) throws SQLException {
		if (cn == null || StringUtils.isBlank(sql)) {
			return -1;
		}

		PreparedStatement pstmt = null;

		try {
			pstmt = cn.prepareStatement(sql);
			if (params != null && params.length > 0) {
				for (int i = 0; i < params.length; i++) {
					pstmt.setObject((i + 1), params[i]);
				}
			}
			return pstmt.executeUpdate();
		} finally {
			close(pstmt);
		}
	}

	private static int executeUpdate0(Connection cn, String sql) throws SQLException {
		if (cn == null || StringUtils.isBlank(sql)) {
			return -1;
		}
		Statement stmt = null;

		try {
			stmt = cn.createStatement();
			return stmt.executeUpdate(sql);
		} finally {
			close(stmt);
		}
	}

	private static String[] getColumns(ResultSet rs) throws SQLException {
		ResultSetMetaData rsmd = rs.getMetaData();
		int count = rsmd.getColumnCount();
		if (count <= 0) {
			return null;
		}
		String[] cols = new String[count];
		String label = null;
		for (int i = 0; i < count; i++) {
			label = rsmd.getColumnLabel(i + 1);
			label = label.toLowerCase();
			cols[i] = label;
		}
		return cols;
	}

	private static Map<String, String> getRowAsMap(ResultSet rs, String[] cols, int count) throws SQLException {
		Map<String, String> row = new HashMap<String, String>();
		for (int i = 0; i < count; i++) {
			row.put(cols[i], rs.getString(i + 1));
		}
		return row;
	}
	
	
	public static Result query(Connection cn, String db,String sql, int maxRows) throws Exception {

		if (maxRows <= 0) {
			maxRows = MAX_ROWS;
		}
		if(maxRows>MAX_ROWS_MAX){
			maxRows = MAX_ROWS_MAX;
		}
		
		Result result = new Result();
		result.sql = sql;
		result.maxRows = maxRows;
		result.db = db;
		Statement stmt = null;
		ResultSet rs = null;
		try {
			stmt = cn.createStatement();
			stmt.setMaxRows(maxRows);

			rs = stmt.executeQuery(sql);
			ResultSetMetaData rsmd = rs.getMetaData();
			int count = rsmd.getColumnCount();
			result.columnCount = count;
			List<Colinfo> colinfos = getColinfos(rsmd, count);

			result.colinfos = colinfos;

			List rows = new ArrayList();
			Object[] row = null;
			while (rs.next()) {
				row = getRow(rs, count);
				rows.add(row);
			}
			result.rows = rows;
			return result;
		} finally {
			close(rs);
			close(stmt);
		}
	}
	
	

	private static Colinfo getColinfo(ResultSetMetaData rsmd, int colindex) throws SQLException {
		Colinfo info = new Colinfo();

		//info.name = rsmd.getColumnName(colindex);
		
		info.name = rsmd.getColumnLabel(colindex);

		info.type = rsmd.getColumnType(colindex);
		info.typeName = rsmd.getColumnTypeName(colindex);
		info.typeClassName = rsmd.getColumnClassName(colindex);

		info.precision = rsmd.getPrecision(colindex);
		info.scale = rsmd.getScale(colindex);

		info.schemaName = rsmd.getSchemaName(colindex);
		info.tableName = rsmd.getTableName(colindex);
		info.catalogName = rsmd.getCatalogName(colindex);

		info.autoIncrement = rsmd.isAutoIncrement(colindex);
		info.nullable = rsmd.isNullable(colindex);
		info.displaySize = rsmd.getColumnDisplaySize(colindex);
		info.label = rsmd.getColumnLabel(colindex);

		return info;
	}

	private static List<Colinfo> getColinfos(ResultSetMetaData rsmd, int count) throws SQLException {
		if (count <= 0) {
			return null;
		}
		List<Colinfo> list = new ArrayList<Colinfo>();
		Colinfo info = null;
		for (int i = 1; i <= count; i++) {
			info = getColinfo(rsmd, i);
			list.add(info);
		}
		return list;
	}

	private static Object[] getRow(ResultSet rs, int count) throws SQLException {
		if (count <= 0) {
			return null;
		}
		Object[] row = new Object[count];
		for (int i = 0; i < count; i++) {
			//
			//row[i] = rs.getObject(i + 1);
			// getObject 方法无法读取clob blob字段
			row[i] = rs.getString(i + 1);
		}

		return row;
	}

}
